lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-10-06(腾工).sql

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePiece')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePiece
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePiece]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePiece]
AS
SELECT   
tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
Ord_Type,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_ChineseToSpell((select stuff((select '',''+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,''''))) as 接单人拼音, 
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
(case when Ord_Type = ''1'' then (select Ordpg_Sights from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber) else '''' end) as 拍摄名称,
(case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''2'') > 0 then ''拍摄中'' 
else case when (select Count(Ordpg_PhotographyStatus) from tb_ErpOrdersPhotography where Ordpg_ViceNumber = Ordv_ViceNumber and Ordpg_PhotographyStatus = ''1'') > 0 then ''OK'' else ''未拍'' end end) AS 拍照状态,
dbo.fn_CheckOrderDesignerStatus(dbo.tb_ErpOrderDigital.Ordv_DesignerStatus) AS 设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_DesignerName) AS 设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_DesignerTime)) AS 设计日期, 
dbo.fn_CheckOrderLookDesignStatus(dbo.tb_ErpOrderDigital.Ordv_LookDesignStatus) AS 看设计状态, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrderDigital.Ordv_LookDesignName) AS 看设计师, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrderDigital.Ordv_LookDesignTime)) AS 看设计日期, 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''0'' and OPlist_CompletedStatus = ''1'')>0 then ''未完'' else 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''0'') > 0 then ''未完'' else 
(case when (select count(OPlist_CompletedStatus) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type = ''2'' and OPlist_CompletedStatus = ''1'') > 0 then ''OK'' else '''' end )end )end ) AS 是否完成,
--(select top 1 dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_PickupTime != '''' order by OPlist_PickupTime DESC) AS 取件日期,
--(select top 1 OPlist_PickupTime from dbo.tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber and OPlist_Type=''2'' and OPlist_PickupTime != '''' order by OPlist_PickupTime DESC) AS 取件日期查询,
tb_ErpOrderProductList.ID AS OPID,
OPlist_ProdName AS 商品名称,
dbo.fn_CheckIsExpedited(OPlist_IsExpedited) AS 加急,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_ReservationTakeTime)) AS 预约取件日期,
OPlist_ReservationTakeTime AS 预约取件日期查询,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(OPlist_PickupTime)) AS 商品取走日期,
OPlist_PickupTime AS 商品取走日期查询,
OPlist_SendVendor AS 是否发出,
dbo.fn_CheckProductReworkStatus(OPlist_ReworkStatus) AS 是否返工,
dbo.fn_CheckProductCompletedStatus(OPlist_CompletedStatus) AS 商品完成状态, 
dbo.fn_CheckProductTakePiecesStatus(OPlist_PickupStatus) AS 是否取走
,Case when (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber)>0 then  (select Count(ID) from tb_ErpOrderProductList where OPlist_ViceNumber = Ordv_ViceNumber) else (SELECT COUNT(Ordv_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrderDigital WHERE Ordv_Number = dbo.tb_ErpOrder.Ord_Number) end   AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number
Left Join tb_ErpOrderProductList ON OPlist_ViceNumber = Ordv_ViceNumber 
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_CameraControlBookMainPhotographed')
BEGIN
	DROP VIEW [dbo].BView_CameraControlBookMainPhotographed
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_CameraControlBookMainPhotographed]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_CameraControlBookMainPhotographed]
AS
SELECT  tb_ErpOrder.ID, 
dbo.tb_ErpOrder.Ord_DividedShop, 
Ord_Type,
dbo.tb_ErpOrdersPhotography.ID AS VID, 
dbo.tb_ErpOrder.Ord_Number, 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber, 
Ordv_DigitalNumber,
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
Cus_Name AS 客户姓名,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.fn_ConvertDate(GregorianCalendar_DayForMarriage))) AS 婚期,
(select stuff((select '','' + dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('''')),1,1,'''')) as 接单人,
dbo.fn_GetOrderArrears(Ord_Number) AS 欠款,
dbo.tb_ErpOrder.Ord_SeriesName AS 套系名称, 
dbo.tb_ErpOrder.Ord_SeriesPrice AS 套系价格, 
dbo.tb_ErpOrdersPhotography.Ordpg_Sights AS 拍摄名称, 
Ordpg_ApparelQuantity AS 服装套数,
dbo.fn_CheckPhotographyStatus(Ordpg_PhotographyStatus) AS 拍摄状态,
dbo.fn_CheckDateTime(Ordpg_ReservationPhotographyTime) AS 摄影时间, 
Ordpg_ReservationPhotographyTime AS 摄影时间查询, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyName) AS 摄影师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationPhotographyAssistant) AS 摄影助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupArtist) AS 化妆师, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationMakeupAssistant) AS 化妆助理, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_ReservationBootDivision) AS 引导师, 
dbo.tb_ErpOrdersPhotography.Ordpg_Remark AS 录入备注, 
dbo.fn_CheckUserIDGetUserName(dbo.tb_ErpOrdersPhotography.Ordpg_Dispatcher) AS 安排人, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(dbo.tb_ErpOrdersPhotography.Ordpg_DispatchTime)) AS 安排时间,
(SELECT COUNT(tb_ErpOrdersPhotography_1.Ordpg_ViceNumber) AS Expr1 FROM dbo.tb_ErpOrdersPhotography AS tb_ErpOrdersPhotography_1 LEFT OUTER JOIN
dbo.tb_ErpOrderDigital AS tb_ErpOrderDigital_1 ON tb_ErpOrdersPhotography_1.Ordpg_ViceNumber = tb_ErpOrderDigital_1.Ordv_ViceNumber WHERE (tb_ErpOrderDigital_1.Ordv_Number = dbo.tb_ErpOrder.Ord_Number)) AS Ord_ViceOrderCount
FROM dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrdersPhotography ON 
dbo.tb_ErpOrderDigital.Ordv_ViceNumber = dbo.tb_ErpOrdersPhotography.Ordpg_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (dbo.tb_ErpOrder.Ord_Type IN (''0'', ''1'', ''2''))
' 
GO


if not exists
(select * from syscolumns where id=object_id('tb_ErpOrdersPhotography') and name='Ordpg_SightsRemark')
begin
	alter table tb_ErpOrdersPhotography add Ordpg_SightsRemark  nvarchar(800) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpOrdersPhotography', N'COLUMN',N'Ordpg_SightsRemark'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'景点备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpOrdersPhotography', @level2type=N'COLUMN',@level2name=N'Ordpg_SightsRemark'
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityOpenOrderFriendSelect')
BEGIN
	DROP VIEW [dbo].BView_DoorCityOpenOrderFriendSelect
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityOpenOrderFriendSelect]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityOpenOrderFriendSelect]
AS
SELECT   
dbo.tb_ErpCustomer.ID, 
Cus_CustomerNumber, 
Cus_Type,
Cus_Name, 
Cus_NamePinyin,
Cus_Telephone, 
Cus_Sex, 
Mc_CradNumber
FROM dbo.tb_ErpCustomer LEFT OUTER JOIN
dbo.tb_ErpMemberCard ON 
Cus_CustomerNumber = Mc_CustomerNumber AND 
Mc_CradNumber <> ''''
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_MemberMain_GoldMember')
BEGIN
	DROP VIEW [dbo].BView_MemberMain_GoldMember
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_MemberMain_GoldMember]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_MemberMain_GoldMember]
AS
SELECT   Cus_ID, ID AS Mc_ID, 
Mc_Status, 
Mc_Number, 
Mc_CradNumber AS 会员卡号, 
Cus_Name AS 姓名, 
Cus_CustomerNumber AS 姓名编号,
dbo.fn_CheckSex(Cus_Sex) AS 性别, 
Cus_Telephone AS 电话, 
Mc_CardType AS 类型, 
dbo.fn_GetMember(Mc_Number, 1) AS 积分, 
Mc_Money AS 金额, 
dbo.fn_GetMember(Mc_Number, 2) AS 折扣, 
dbo.fn_GetMember(Mc_Number, 3) AS 套系金额, 
dbo.fn_GetMember(Mc_Number, 4) AS 已返金额, 
dbo.fn_GetMember(Mc_Number, 5) AS 已取出金额,
(SELECT COUNT(Mcse_ServiceCount) AS Expr1 FROM dbo.tb_ErpMemberCardServiceContent WHERE (Mcse_Number = dbo.BView_MemberCard.Mc_Number)) AS 服务次数,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''积分副卡'')) AS 积分副卡,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard AS tb_ErpMemberViceCard_2 WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''现金副卡'')) AS 现金副卡,
(SELECT COUNT(Mvc_Number) AS Expr1 FROM dbo.tb_ErpMemberCardViceCard AS tb_ErpMemberViceCard_1 WHERE (Mvc_Number = dbo.BView_MemberCard.Mc_Number) AND (Mvc_Status = ''1'') AND (Mvc_ViceCardType = ''返现副卡'')) AS 返现副卡, 
Mc_Remark AS 备注, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(Mc_CreateDatetime)) AS 办卡日期
FROM      dbo.BView_MemberCard
WHERE   (Mc_Status = ''1'')
' 
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'BView_DoorCityTotakePieceMonthReworkStatistic')
BEGIN
	DROP VIEW [dbo].BView_DoorCityTotakePieceMonthReworkStatistic
END 
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[BView_DoorCityTotakePieceMonthReworkStatistic]
AS
SELECT   
tb_ErpOrder.ID, dbo.tb_ErpOrder.Ord_DividedShop, dbo.tb_ErpOrder.Ord_Type, tb_ErpOrderProductList_1.ID AS PPID, 
dbo.tb_ErpOrder.Ord_Number, dbo.tb_ErpOrderDigital.Ordv_ViceNumber,
Ordv_DigitalNumber, 
dbo.fn_CheckOrderType(dbo.tb_ErpOrder.Ord_Type) AS 订单类型, 
CASE [Ord_SinceOrderNumber] WHEN '''' THEN Ord_Number ELSE Ord_SinceOrderNumber END AS 订单号,
Cus_Name AS 客户姓名,
Cus_Name_py AS 客户拼音,
Cus_Sex_cs AS 客户性别,
Cus_Telephone AS 客户电话,
tb_ErpOrderProductList_1.OPlist_ProdNumber, tb_ErpOrderProductList_1.OPlist_ProdName AS 产品名称, 
tb_ErpOrderProductList_1.OPlist_Class,
(SELECT Sc_ClassName FROM dbo.tb_ErpSystemCategory WHERE (Sc_ClassCode = tb_ErpOrderProductList_1.OPlist_Class)) AS 商品类别, 
tb_ErpOrderProductList_1.OPlist_ProdQuantity AS 数量, tb_ErpOrderProductList_1.OPlist_SendVendor AS 是否发出, 
dbo.fn_CheckProductReworkStatus(tb_ErpOrderProductList_1.OPlist_ReworkStatus) 
+ CASE OPlist_ReworkRemark WHEN '''' THEN '''' ELSE ''/['' + OPlist_ReworkRemark + '']'' END AS 是否返工, 
OPlist_ReworkRemark2 AS 返工原因,
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_ReworkTime)) AS 返工日期, 
tb_ErpOrderProductList_1.OPlist_ReworkTime AS 返工日期查询, 
dbo.fn_CheckProductCompletedStatus(tb_ErpOrderProductList_1.OPlist_CompletedStatus) AS 是否完成, 
dbo.fn_CheckDateTimeReturn_Date(dbo.fn_CheckDateTime(tb_ErpOrderProductList_1.OPlist_CompletedTime)) AS 完成日期,
(SELECT   COUNT(ID) AS Expr1 FROM      dbo.tb_ErpOrderProductList
WHERE   (OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber) AND (OPlist_Type = ''2'')) AS Ord_ViceOrderCount
FROM      dbo.tb_ErpOrder LEFT OUTER JOIN
dbo.tb_ErpOrderDigital ON dbo.tb_ErpOrder.Ord_Number = dbo.tb_ErpOrderDigital.Ordv_Number LEFT OUTER JOIN
dbo.tb_ErpOrderProductList AS tb_ErpOrderProductList_1 ON 
tb_ErpOrderProductList_1.OPlist_ViceNumber = dbo.tb_ErpOrderDigital.Ordv_ViceNumber
left join tempTB_AggregationCustomer on tb_ErpOrder.Ord_Number = GP_OrderNumber
WHERE   (dbo.tb_ErpOrder.Ord_Class = ''1'') AND (tb_ErpOrderProductList_1.OPlist_Type = ''2'')
' 
GO